﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VideoClassLibrary.Model;
using VideoClassLibrary.BusinessLogicLayer;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace VideoClassLibrary.BusinessLogicLayer
{

   public class SchoolModule
    {
       /// <summary>
       /// 查询学校logo
       /// </summary>
       /// <param name="id">schoolid</param>
       /// <returns></returns>
       public DataSet QuerySchoolById(int id)
       {

           StringBuilder strSql = new StringBuilder();
           strSql.Append("  select [ID],create_time,update_time,VS_type,VS_name,VS_englishname,VS_phone,VS_address,VS_zipcode,VS_city,VS_province,VS_Desc,VS_state,VS_logofile,VS_relanname,VS_relanamephone,VS_relanameemail,VS_schoolImg from V_school where ID=@id");
           Database db = VDataBase.CreateDatabase();
           DbCommand command = db.GetSqlStringCommand(strSql.ToString());
           db.AddInParameter(command, "id", DbType.Int32, id);
           return db.ExecuteDataSet(command);
       }
       /// <summary>
        /// 得到当前用户所在的学校实体
       /// </summary>
       /// <param name="ID"></param>
       /// <returns></returns>
        public V_School GetSchoolModelById(int schoolId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,create_time,update_time,VS_type,VS_name,VS_englishname,VS_phone,VS_address,VS_zipcode,VS_city,VS_province,VS_Desc,VS_state,VS_logofile,VS_relanname,VS_relanamephone,VS_relanameemail,VS_schoolImg from V_school  ");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "ID", DbType.Int32, schoolId);
            DataSet dsResult = db.ExecuteDataSet(command);
            V_School model = null;

            if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
            {
                DataRow dr = dsResult.Tables[0].Rows[0];
                model = new V_School();
                model.create_time = dr["create_time"] == DBNull.Value ? System.DateTime.Now : System.DateTime.Parse(dr["create_time"].ToString());
                model.ID = int.Parse(dr["ID"].ToString());
                model.VS_type = int.Parse(dr["VS_type"].ToString());
                model.VS_name = dr["VS_name"].ToString();
                model.VS_englishname = dr["VS_englishname"].ToString();
                model.VS_phone =dr["VS_phone"].ToString();
                model.Vs_desc = dr["VS_desc"].ToString();
                model.VS_address =dr["VS_address"].ToString();
                model.VS_zipcode = dr["VS_zipcode"].ToString();
                model.VS_city = dr["VS_city"].ToString();
                model.VS_province = dr["VS_province"].ToString();
                model.VS_state =dr["VS_state"]==DBNull.Value?0: int.Parse(dr["VS_state"].ToString());
                model.VS_logofile = dr["VS_logofile"].ToString();
                model.VS_relanname = dr["VS_relanname"].ToString();
                model.VS_relanamephone = dr["VS_relanamephone"].ToString();
                model.VS_relanameemail = dr["VS_relanameemail"].ToString();
                model.Vs_SchoolImg = dr["VS_schoolImg"].ToString();
    
                dsResult = null;
            }
            return model;
        }




       /// <summary>
       /// 查询学校所有信息
       /// </summary>
       /// <returns></returns>
        public DataSet QueryAllSchool()
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from V_school order by create_time desc");
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text,strSql.ToString());
        }
        /// <summary>
        /// 获取查询条件的所有学校
        /// </summary>
        public DataSet GetSchoolListByWhere(string whereSql)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,create_time,update_time,VS_type,VS_name,VS_englishname,VS_desc,VS_phone,VS_address,VS_zipcode,VS_city,VS_province,VS_state,VS_logofile,VS_relanname,VS_relanamephone,VS_relanameemail,VS_schoolImg ");
            strSql.Append(" FROM V_school where 1=1 ");

            if (whereSql != "")
            {
                strSql.Append(whereSql);
            }
            Database db = VDataBase.CreateDatabase();

            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
        //#region 修改学校信息
        //public int UpdateSchool(V_School school)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append(" update V_school set  update_time=@Update_time,VS_type=@VS_type,VS_name=@VS_name,VU_loginname=@VU_loginname,VU_Password=@VU_Password,VU_realname=@VU_realname,VU_mobile=@VU_mobile,VU_email=@VU_email where  ID=@id");
        //    strSql.Append(";select @@IDENTITY");
        //    Database db = VDataBase.CreateDatabase();
        //    DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
        //    db.AddInParameter(dbCommand, "id", DbType.Int32, user.ID);
        //    db.AddInParameter(dbCommand, "Update_time", DbType.DateTime, user.update_time);
        //    db.AddInParameter(dbCommand, "VU_loginname", DbType.AnsiString, user.VU_loginname);
        //    db.AddInParameter(dbCommand, "VU_Password", DbType.AnsiString, MD5Enctrypt(user.VU_Password));
        //    db.AddInParameter(dbCommand, "VU_realname", DbType.AnsiString, user.VU_realname);
        //    db.AddInParameter(dbCommand, "VU_mobile", DbType.AnsiString, user.VU_mobile);
        //    db.AddInParameter(dbCommand, "VU_email", DbType.AnsiString, user.VU_email);
        //    db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, user.SchoolID);
        //    int result = db.ExecuteNonQuery(dbCommand);
        //    if (result > 0)
        //    {
        //        return 1;
        //    }
        //    else
        //    {
        //        return -1;
        //    }
        //}
        //#endregion
        /// <summary>
        /// 添加学校
       /// </summary>
       /// <param name="ID"></param>
       /// <returns></returns> 
        public int AddSchool(V_School model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into V_school (");
            strSql.Append("create_time,update_time,VS_type,VS_name,VS_englishname,VS_phone,VS_address,VS_desc,VS_zipcode,VS_city,VS_province,VS_state,VS_logofile,VS_relanname,VS_relanamephone,VS_relanameemail,VS_schoolImg)");
            strSql.Append(" values (");
            strSql.Append(" @create_time,@update_time,@VS_type,@VS_name,@VS_englishname,@VS_phone,@VS_address,@vs_desc@VS_zipcode,@VS_city,@VS_province,@VS_state,@VS_logofile,@VS_relanname,@VS_relanamephone,@VS_relanameemail,@VS_schoolImg)");
            strSql.Append(";select @@IDENTITY");
              Database db = VDataBase.CreateDatabase();
             DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
             db.AddInParameter(dbCommand, "create_time", DbType.DateTime, model.create_time);
             db.AddInParameter(dbCommand, "update_time", DbType.DateTime, model.update_time);
             db.AddInParameter(dbCommand, "VS_type", DbType.Int32, model.VS_type);
             db.AddInParameter(dbCommand, "VS_name", DbType.AnsiString, model.VS_name);
             db.AddInParameter(dbCommand, "VS_englishname", DbType.AnsiString, model.VS_englishname);
             db.AddInParameter(dbCommand, "VS_phone", DbType.AnsiString, model.VS_phone);
             db.AddInParameter(dbCommand, "VS_address", DbType.AnsiString, model.VS_address);
             db.AddInParameter(dbCommand, "vs_desc", DbType.AnsiString, model.Vs_desc);
             db.AddInParameter(dbCommand, "VS_zipcode", DbType.AnsiString, model.VS_zipcode);
             db.AddInParameter(dbCommand, "VS_city", DbType.AnsiString, model.VS_city);
             db.AddInParameter(dbCommand, "VS_province", DbType.AnsiString, model.VS_province);
             db.AddInParameter(dbCommand, "VS_state", DbType.Int32, model.VS_state);
             db.AddInParameter(dbCommand, "VS_logofile", DbType.AnsiString, model.VS_logofile);
             db.AddInParameter(dbCommand, "VS_relanname", DbType.AnsiString, model.VS_relanname);
             db.AddInParameter(dbCommand, "VS_relanamephone", DbType.AnsiString, model.VS_relanamephone);
             db.AddInParameter(dbCommand, "VS_relanameemail", DbType.AnsiString, model.VS_relanameemail);
             db.AddInParameter(dbCommand, "VS_schoolImg", DbType.AnsiString, model.Vs_SchoolImg);
             int result;
             try
             {
                 object obj = db.ExecuteScalar(dbCommand);
                 if (!int.TryParse(obj.ToString(), out result))
                 {
                     return 0;
                 }
             }
             catch
             {
                 return 0;
             }
             return result;

        }
       /// <summary>
       /// 添加新学校
       /// </summary>
       /// <param name="model"></param>
       /// <returns></returns>
        public int AddNewSchool(V_School model)
        {
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("Proc_AddNewSchool");
            db.AddOutParameter(dbCommand, "ID", DbType.Int32, 32);
            db.AddInParameter(dbCommand, "VS_type", DbType.Int32, model.VS_type);
            db.AddInParameter(dbCommand, "VS_name", DbType.AnsiString, model.VS_name);
            db.AddInParameter(dbCommand, "VS_englishname", DbType.AnsiString, model.VS_englishname);
            db.AddInParameter(dbCommand, "VS_phone", DbType.AnsiString, model.VS_phone);
            db.AddInParameter(dbCommand, "VS_address", DbType.AnsiString, model.VS_address);
            db.AddInParameter(dbCommand, "VS_zipcode", DbType.AnsiString, model.VS_zipcode);
            db.AddInParameter(dbCommand, "VS_city", DbType.AnsiString, model.VS_city);
            db.AddInParameter(dbCommand, "VS_desc", DbType.AnsiString, model.Vs_desc);
            db.AddInParameter(dbCommand, "VS_province", DbType.AnsiString, model.VS_province);
            db.AddInParameter(dbCommand, "VS_state", DbType.Int32, model.VS_state);
            db.AddInParameter(dbCommand, "VS_logofile", DbType.AnsiString, string.Empty);
            db.AddInParameter(dbCommand, "VS_relanname", DbType.AnsiString, model.VS_relanname);
            db.AddInParameter(dbCommand, "VS_relanamephone", DbType.AnsiString, model.VS_relanamephone);
            db.AddInParameter(dbCommand, "VS_relanameemail", DbType.AnsiString, model.VS_relanameemail);
            db.AddInParameter(dbCommand, "VS_schoolImg", DbType.AnsiString, model.Vs_SchoolImg);
            db.AddInParameter(dbCommand, "vs_web", DbType.AnsiString, model.Vs_web);
            int schoolId;

            try
            {
                db.ExecuteNonQuery(dbCommand);

                object obj =db.GetParameterValue(dbCommand, "ID");
                if (!int.TryParse(obj.ToString(), out schoolId))
                {
                    return 0;
                }
            }
            catch
            {
                return 0;
            }
            return schoolId;
        }
        /// <summary>
        /// 修改学校
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int UpdateSchool(V_School model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update V_school set ");
            strSql.Append("update_time=@update_time,");
            strSql.Append("VS_type=@VS_type,");
            strSql.Append("VS_name=@VS_name,");
            strSql.Append("VS_englishname=@VS_englishname,");
            strSql.Append("VS_phone=@VS_phone,");
            strSql.Append("VS_address=@VS_address,");
            strSql.Append("VS_zipcode=@VS_zipcode,");
            strSql.Append("VS_city=@VS_city,");
            strSql.Append("VS_province=@VS_province,");
            strSql.Append("VS_state=@VS_state,");
            strSql.Append("VS_logofile=@VS_logofile,");
            strSql.Append("VS_relanname=@VS_relanname,");
            strSql.Append("VS_desc=@VS_desc,");
            strSql.Append("VS_relanamephone=@VS_relanamephone,");
            strSql.Append("vs_web=@vs_web,");
            strSql.Append("VS_relanameemail=@VS_relanameemail,");
            strSql.Append("Vs_SchoolImg=@Vs_SchoolImg");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, model.ID);
            db.AddInParameter(dbCommand, "update_time", DbType.DateTime, model.update_time);
            db.AddInParameter(dbCommand, "VS_type", DbType.Int32, model.VS_type);
            db.AddInParameter(dbCommand, "VS_name", DbType.AnsiString, model.VS_name);
            db.AddInParameter(dbCommand, "VS_englishname", DbType.AnsiString, model.VS_englishname);
            db.AddInParameter(dbCommand, "VS_phone", DbType.AnsiString, model.VS_phone);
            db.AddInParameter(dbCommand, "VS_address", DbType.AnsiString, model.VS_address);
            db.AddInParameter(dbCommand, "VS_zipcode", DbType.AnsiString, model.VS_zipcode);
            db.AddInParameter(dbCommand, "VS_city", DbType.AnsiString, model.VS_city);
            db.AddInParameter(dbCommand, "VS_province", DbType.AnsiString, model.VS_province);
            db.AddInParameter(dbCommand, "VS_state", DbType.Int32, model.VS_state);
            db.AddInParameter(dbCommand, "VS_logofile", DbType.AnsiString, model.VS_logofile);
            db.AddInParameter(dbCommand, "VS_relanname", DbType.AnsiString, model.VS_relanname);
            db.AddInParameter(dbCommand, "VS_desc", DbType.AnsiString, model.Vs_desc);
            db.AddInParameter(dbCommand, "VS_relanamephone", DbType.AnsiString, model.VS_relanamephone);
            db.AddInParameter(dbCommand, "VS_relanameemail", DbType.AnsiString, model.VS_relanameemail);
            db.AddInParameter(dbCommand, "vs_web", DbType.String, model.Vs_web);
            
            db.AddInParameter(dbCommand, "Vs_SchoolImg", DbType.AnsiString, model.Vs_SchoolImg);
            int result = db.ExecuteNonQuery(dbCommand);
            if (result > 0)
            { return 1; }
            else
            { return -1; }

        }

        /// <summary>
        /// 判断是否可以添加
        /// </summary>
        public bool IsName(string name)
        {
            string sql = "select VS_name from V_school where VS_name=@name ";
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(sql);
            db.AddInParameter(command, "name", DbType.String, name);
            DataSet dsResult = db.ExecuteDataSet(command);
            if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
            {
                DataRow dr = dsResult.Tables[0].Rows[0];
                string VS_names = dr["VS_name"].ToString();
                if (VS_names == "" || VS_names == null)
                {
                    return true;

                }
                else
                { return false; }
            }
            else
            { return true; }
        }
        /// <summary>
        /// 根据学校ID删除学校
        /// 
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int DeleteSchool(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from V_school ");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, ID);
            int result = db.ExecuteNonQuery(dbCommand);
            if (result > 0)
            { return 1; }
            else
            { return -1; }
        }

        /// <summary>
        ///根据ID 修改学校简介图片
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int SaveSchoolImageFile(string id, string schoolImageFile)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update V_school  set Vs_SchoolImg=@Vs_SchoolImg  ");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "Vs_SchoolImg", DbType.AnsiString, schoolImageFile);
            db.AddInParameter(command, "ID", DbType.Int32, id);
            return db.ExecuteNonQuery(command);
        }

        /// <summary>
        ///根据ID 修改学校图标
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int SaveLogoFile(string id, string logoFile)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update V_school  set vs_logoFile=@logoFile ");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "logoFile", DbType.AnsiString, logoFile);
            db.AddInParameter(command, "ID", DbType.Int32, id);
            return db.ExecuteNonQuery(command);
        }

    }
   public class Common
   {
       private static List<string> _schoolTypes = new List<string> { "普通院校", "高等院校", "特级院校" };

       public static string[] GetSchoolTypes()
       {
           return _schoolTypes.ToArray();
       }
       public static string GetSchoolTypeName(int id)
       {
           if (id - 1 >= 0 && id - 1 < _schoolTypes.Count)
               return _schoolTypes[id - 1];
           else
               return "";
       }
       public static string GetSchoolTypeName(object id)
       {
           if (id is DBNull)
           {
               return "";
           }
           else
           {
               return GetSchoolTypeName((int)id);
           }
       }
       public static int GetSchooID(string name)
       {
           for (int i = 0; i < _schoolTypes.Count; i++)
           {
               if (name.Equals(_schoolTypes[i]))
               {
                   return i + 1;
               }
           }
           return -1;
       }


   }
   public class SysSetting
   {
       public static string UploadFileDir = "Upload";
       public static string GetUploadPath()
       {
           string path = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + @"\" + UploadFileDir;
           if (!System.IO.Directory.Exists(path))
               System.IO.Directory.CreateDirectory(path);
           return path;
       }

   }


    
}
